package com.dhcc.DRGsHosp.application.impl;

import java.io.Serializable;
import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.inject.Named;

import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.dayatang.querychannel.QueryChannelService;
import org.dayatang.utils.Page;
import org.openkoala.koalacommons.resourceloader.util.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.MedicalRecordApplication;
import com.dhcc.DRGsHosp.core.domain.IndexCal;
import com.dhcc.DRGsHosp.core.domain.MedicalRecord;
import com.dhcc.DRGsHosp.core.domain.MrAppendPage;
import com.dhcc.DRGsHosp.core.domain.MrBaseInfo;
import com.dhcc.DRGsHosp.core.domain.MrDiagnose;
import com.dhcc.DRGsHosp.core.domain.MrExpense;
import com.dhcc.DRGsHosp.core.domain.MrExpenseSum;
import com.dhcc.DRGsHosp.core.domain.MrIpInfo;
import com.dhcc.DRGsHosp.core.domain.MrOperation;
import com.dhcc.DRGsHosp.infra.util.MonitorResultUtil;

@Named
@Transactional
public class MedicalRecordApplicationImpl implements MedicalRecordApplication,Serializable {
	private static final long serialVersionUID = 1L;
	private QueryChannelService queryChannel;
	
	@Autowired
	private JdbcTemplateObj jdbcTemplateObj;
	
    private QueryChannelService getQueryChannelService(){
       if(queryChannel==null){
          queryChannel = InstanceFactory.getInstance(QueryChannelService.class,"queryChannel");
       }
     return queryChannel;
    }
    
    private EntityRepository repository;
	
	private EntityRepository getRepository() {
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}
    
	public MedicalRecord getMedicalRecord(String id) {
		return MedicalRecord.get(MedicalRecord.class, id);
	}
	
	public void creatMedicalRecord(MedicalRecord medicalRecord) {
		medicalRecord.save();
	}
	
	public void updateMedicalRecord(MedicalRecord medicalRecord) {
		medicalRecord .save();
	}
	
	public void removeMedicalRecord(MedicalRecord medicalRecord) {
		if(medicalRecord != null){
			medicalRecord.remove();
		}
	}
	
	public void removeMedicalRecords(Set<MedicalRecord> medicalRecords) {
		for (MedicalRecord medicalRecord : medicalRecords) {
			medicalRecord.remove();
		}
	}
	
	public List<MedicalRecord> findAllMedicalRecord() {
		return MedicalRecord.findAll(MedicalRecord.class);
	}
	
	@Override
	public List<Map<String, Object>> getMedicalRecordYbNum(String year,String hospCode){
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		String sql="";
		sql =  "SELECT TO_CHAR(IP.P00290000,'MM'),COUNT(M.PARID) \n"
				+ "FROM drgs.mr_baseinfo bs left join drgs.mr_ipinfo ip on bs.pmainindex=ip.pmainindex  \n"
				+ "LEFT JOIN DRGS.T_MEDINS M ON IP.PMAININDEX=M.PARID \n"
				+ "WHERE TO_CHAR(IP.P00290000,'YYYY')='"+year+"' \n"
				+ "AND M.PARID IS NOT NULL group by TO_CHAR(IP.P00290000,'MM') and instr('"+hospCode+"',bs.p00010001)>0 ";
		List<Object[]>  mdList = getQueryChannelService().createSqlQuery(sql.toString()).list();
		for (Object[] objects : mdList) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("month", objects[0]);
			map.put("num", objects[1]);
			
			resultList.add(map);
		}
		return resultList;
	}
	
	/* (non-Javadoc)
	 * @see com.dhcc.DRGs.application.MedicalRecordApplication#findMedicalRecord(com.dhcc.DRGs.core.domain.IndexCal)
	 */
	@Override
	public Page<Object[]> findMedicalRecord(int page, int pageSize,IndexCal IndexCal,Boolean isAllUsers){
		if(pageSize==-1){
			pageSize = 99999999;
		}
		StringBuilder sql = new StringBuilder();
//		String mrLot = IndexCal.getMrLot();
		String mrType = IndexCal.getMrType();
		String pType = IndexCal.getpType();
		String depGroupCode = IndexCal.getPardepgroupCode();
		String depCode = IndexCal.getPardepCode();
		String groupName = IndexCal.getPargroupName();
		String wardCode = IndexCal.getParwardCode();
		String protomedicus = IndexCal.getProtomedicus();
		String mdcCode = IndexCal.getParmdcCode();
		String adrgCode = IndexCal.getParadrgCode();
		String drgCode = IndexCal.getPardrgCode();
		String dateFrom = IndexCal.getDateFrom();
		String physician = IndexCal.getPhysician();
		String resident = IndexCal.getResident();
		dateFrom = MonitorResultUtil.getStartDateMonth(dateFrom);
		String dateTo = IndexCal.getDateTo();
		dateTo = MonitorResultUtil.getEndDateMonth(dateTo);
//		if(StringUtils.hasLength(mrLot)){
//			dateFrom = MonitorResultUtil.getStartDateByLot(mrLot, mrType);
//			dateTo = MonitorResultUtil.getEndDateByLot(mrLot, mrType);
//		}
		dateFrom = dateFrom + " 0:00:00";
        dateTo = dateTo + " 23:59:59";
//		String stdYear = IndexCal.getMrDateYear();
		String manageYear = IndexCal.getManageYear();
		String type1 = IndexCal.getType1();
		String risk = IndexCal.getType3();
		//河南省医老板系统获取标杆年粉方法
		String stdYear = dateTo.substring(0, 4);
		String sqlYear="select max(as_year) from DRGs.t_Annualstd t";
		Object recentYear = getQueryChannelService().createSqlQuery(sqlYear.toString()).singleResult();
		if(Integer.parseInt(stdYear) > Integer.parseInt(recentYear.toString())){
			stdYear = recentYear.toString();
		}
		//查询药品费用的计算规则
		String costRuleSql = "select t.rule from drgs.MR_COST_RULE t where t.rule_code = 'drugcost'";
		List<String> list = getQueryChannelService().createSqlQuery(costRuleSql).list();
		String drugCost = "''";
		if(!list.isEmpty())
		{
			drugCost=list.get(0);
		}
		
		//药品费用处要从库中动态查询
		sql.append(" select distinct base.P00050000 mr_no, base.P00060000 patient_name, base.P00070001 sex,base.P00090000 age_year,to_char(ip.P00290000,'yyyy-mm-dd') out_date,dep.dep_name ,IP.P00620001 out_way, ip.P00320000 in_days, \n ");
		sql.append(" diag.PD0100 diagnose_dess_main, drg.drg_desc,adrg.adrg_desc,mdc.mdc_desc,base.pmainindex,drg.drg_code,to_char(ip.P00250000,'yyyy-mm-dd') in_date,ip.P00800000 costamount,"+drugCost+" DRUGCOST,std.as_weight,manageAvgFee.mAvgFee, manageAvgDays.mAvgDays  \n ");
		sql.append(" from \n ");
		sql.append(" drgs.mr_baseinfo base left join drgs.mr_ipinfo ip  \n ");
		sql.append(" on base.pmainindex = ip.pmainindex \n ");
		if("1".equals(pType)){
	    	sql.append(" right join DRGs.t_medins m \n ");
		    sql.append(" on base.pmainindex = m.parid \n ");
	    }
		sql.append(" left join  drgs.t_department dep  \n ");
		sql.append(" on dep.dep_code = ip.P00300001 \n ");
		sql.append(" left join drgs.t_depgroup dg \n ");
		sql.append(" on dep.pardepgroup_code = dg.depgroup_code \n ");
		sql.append(" left join drgs.t_patientdrg pd \n ");
		sql.append(" on pd.parid = base.pmainindex \n ");
		sql.append(" left join drgs.mr_cost mrcost \n ");
		sql.append(" on base.pmainindex = mrcost.parid  \n ");
		sql.append(" left join DRGs.t_drgs drg \n ");
		sql.append(" on pd.pardrg_code = drg.drg_code and pd.ver_code=drg.ver_code \n ");
		sql.append(" left join DRGs.t_adrg adrg \n ");
		sql.append(" on adrg.adrg_code = drg.adrg_code and adrg.ver_code=drg.ver_code \n ");
		sql.append(" left join DRGs.t_mdcgroup mdc \n ");
		sql.append(" on mdc.mdc_code = adrg.mdc_code and mdc.ver_code=adrg.ver_code \n ");
		sql.append(" left join DRGs.t_annualstd std \n ");
		sql.append(" on drg.drg_code=std.as_drg_code and std.as_year='"+stdYear+"' \n ");
	    sql.append(" left join (   \n ");
	    
	    sql.append(" select pd.pardrg_code edrg_code,   \n ");
	    sql.append(" avg(ip.P00800000) eAvgFee, avg(ip.P00320000) eAvgDays \n ");
	    sql.append(" from DRGs.mr_ipinfo ip       \n ");
	    sql.append(" left join DRGs.t_patientdrg pd        \n ");
	    sql.append(" on ip.pmainindex=pd.parid  \n ");
	    sql.append("  left join drgs.t_drgs drg       \n ");
	    sql.append(" on drg.DRG_CODE=pd.PARDRG_CODE and pd.VER_CODE=drg.VER_CODE       \n ");
	    sql.append(" left join drgs.t_adrg adrg       \n ");
	    sql.append(" on adrg.ADRG_CODE=drg.ADRG_CODE and drg.VER_CODE=adrg.VER_CODE       \n ");
	    sql.append(" left join drgs.t_mdcgroup mdc       \n ");
	    sql.append(" on mdc.MDC_CODE=adrg.MDC_CODE and mdc.VER_CODE=adrg.VER_CODE       \n ");
	    sql.append(" left join DRGs.t_department dep       \n ");
	    sql.append(" on ip.P00300001=dep.dep_code       \n ");
	    sql.append(" left join DRGs.t_Depgroup depgroup       \n ");
	    sql.append(" on depgroup.depgroup_code=dep.pardepgroup_code       \n ");
	    sql.append(" left join DRGs.t_annualstd std       \n ");
	    sql.append(" on pd.pardrg_code=std.as_drg_code and std.as_year='"+stdYear+"'   \n ");
	    sql.append(" left join DRGs.t_medins d       \n ");
	    sql.append(" on d.parid=ip.pmainindex       \n ");
	    sql.append(" where ip.P00290000 between to_date('"+dateFrom+"', 'yyyy-MM-dd hh24:mi:ss') and to_date('"+dateTo+"','yyyy-MM-dd hh24:mi:ss') ");
	    sql.append(" and 1=1    \n ");
	    if(StringUtils.hasLength(depGroupCode) && !"null".equals(depGroupCode)){
			sql.append(" and depgroup.depgroup_code='"+depGroupCode+"'");
		}
	    if(StringUtils.hasLength(depCode) && !"null".equals(depCode)){
			if(depCode.indexOf(",")>-1)
			{//多个科室
				depCode = "'"+depCode.replaceAll(",","','")+"'";
				sql.append("  and dep.dep_code in ("+depCode+") \n ");
			}
			else 
			{//一个科室
				sql.append("  and dep.dep_code = '"+depCode+"'   \n ");
			}
		}
		if(StringUtils.hasLength(wardCode) && !"null".equals(depCode)){
			sql.append(" and ip.P00310001='"+wardCode+"'");
		}
		if(StringUtils.hasLength(groupName) && !"null".equals(groupName)){//主诊医师
			sql.append(" and ip.P00601000='"+groupName+"'");
		}else if("null".equals(groupName)){
			sql.append(" and ip.P00601000 is null");
		}
		if(StringUtils.hasLength(protomedicus) && !"null".equals(protomedicus)){//主任医师
			sql.append(" and ip.P00600200='"+protomedicus+"'");
		}else if("null".equals(protomedicus)){
			sql.append(" and ip.P00600200 is null");
		}
		if(StringUtils.hasLength(physician) && !"null".equals(physician)){//主治医师
			sql.append(" and ip.P00600300='"+physician+"'");
		}else if("null".equals(physician)){
			sql.append(" and ip.P00600300 is null");
		}
		if(StringUtils.hasLength(resident) && !"null".equals(resident)){//住院医师
			sql.append(" and ip.P00600400='"+resident+"'");
		}else if("null".equals(resident)){
			sql.append(" and ip.P00600400 is null");
		}
	    sql.append(" and pd.pardrg_code is not null     \n ");
	    sql.append(" group by pd.pardrg_code   \n ");
	    sql.append(" ) i on i.edrg_code=pd.pardrg_code   \n ");
		sql.append(" left join drgs.mr_diagnose diag  \n ");
		sql.append(" on diag.pmainindex = base.pmainindex and diag.PDType = 'M' \n ");
		sql.append(" left join (  \n ");
		sql.append(" select pardrg_code, manage_benchmark mAvgFee, row_number() over(partition by parquota_code, pardrg_code, manage_year, patient_type order by manage_benchmark ) n from drgs.t_quotamanage  \n ");
		sql.append(" where pardep_code is null  \n ");
		sql.append(" and pardepgroup_code is null  \n ");
		sql.append(" and manage_year = '"+manageYear+"'  \n ");
		sql.append(" and parquota_code='AvgFee'  \n ");
		sql.append(" and patient_type='"+pType+"'  \n ");
		sql.append(" ) manageAvgFee on manageAvgFee.pardrg_code=pd.pardrg_code and manageAvgFee.n=1  \n ");
		sql.append(" left join (  \n ");
		sql.append(" select pardrg_code, manage_benchmark mAvgDays, row_number() over(partition by parquota_code, pardrg_code, manage_year, patient_type order by manage_benchmark ) n from drgs.t_quotamanage  \n ");
		sql.append(" where pardep_code is null  \n ");
		sql.append(" and pardepgroup_code is null  \n ");
		sql.append(" and manage_year = '"+manageYear+"'  \n ");
		sql.append(" and parquota_code='AvgDays'  \n ");
		sql.append(" and patient_type='"+pType+"'  \n ");
		sql.append(" ) manageAvgDays on manageAvgDays.pardrg_code=pd.pardrg_code and manageAvgFee.n=1  where 1=1 \n ");

		if(!StringUtils.hasLength(risk) && isAllUsers!=null &&isAllUsers!=true){
	    	sql.append(" and pd.notin_code is null");
		    sql.append(" and ip.P00320000 <= 60");
	    }
		sql.append(" and ip.P00290000 between to_date('"+dateFrom+"', 'yyyy-MM-dd hh24:mi:ss') and to_date('"+dateTo+"','yyyy-MM-dd hh24:mi:ss') ");
		
		if(StringUtils.hasLength(depGroupCode) && !"null".equals(depGroupCode)){
			sql.append(" and dg.depgroup_code='"+depGroupCode+"'");
		}
		if(StringUtils.hasLength(depCode) && !"null".equals(depCode)){
			if(depCode.indexOf(",")>-1)
			{//多个科室
				sql.append("  and dep.dep_code in ("+depCode+") \n ");
			}
			else 
			{//一个科室
				sql.append("  and dep.dep_code = '"+depCode+"'   \n ");
			}
		}
		if(StringUtils.hasLength(wardCode) && !"null".equals(depCode)){
			sql.append(" and ip.P00310001='"+wardCode+"'");
		}
		if(StringUtils.hasLength(groupName) && !"null".equals(groupName)){//主诊医师
			sql.append(" and ip.P00601000='"+groupName+"'");
		}else if("null".equals(groupName)){
			sql.append(" and ip.P00601000 is null");
		}
		if(StringUtils.hasLength(protomedicus) && !"null".equals(protomedicus)){//主任医师
			sql.append(" and ip.P00600200='"+protomedicus+"'");
		}else if("null".equals(protomedicus)){
			sql.append(" and ip.P00600200 is null");
		}
		if(StringUtils.hasLength(physician) && !"null".equals(physician)){//主治医师
			sql.append(" and ip.P00600300='"+physician+"'");
		}else if("null".equals(physician)){
			sql.append(" and ip.P00600300 is null");
		}
		if(StringUtils.hasLength(resident) && !"null".equals(resident)){//住院医师
			sql.append(" and ip.P00600400='"+resident+"'");
		}else if("null".equals(resident)){
			sql.append(" and ip.P00600400 is null");
		}
		if(StringUtils.hasLength(mdcCode) && ! "null".equals(mdcCode)){
			sql.append(" and mdc.mdc_code='"+mdcCode+"'");
		}else if("null".equals(mdcCode)){
			sql.append(" and mdc.mdc_code is null");
		}
		if(StringUtils.hasLength(adrgCode) && !"null".equals(adrgCode)){
			sql.append(" and adrg.adrg_code='"+adrgCode+"'");
		}else if("null".equals(adrgCode)){
			sql.append(" and adrg.adrg_code is null");
		}
		if(StringUtils.hasLength(drgCode) && !"null".equals(drgCode) && !"未入组".equals(drgCode) && !"未分组".equals(drgCode)){
			sql.append(" and drg.drg_code='"+drgCode+"'");
		}else if("null".equals(drgCode) || "未入组".equals(drgCode) || "未分组".equals(drgCode)){
			sql.append(" and drg.drg_code is null");
		}
		if(StringUtils.hasLength(risk)){
			sql.append(" and ip.P00620001='5' and std.as_risk_level='"+risk+"' ");
		}
		if(StringUtils.hasLength(type1)){
			type1 = type1.replace("\"", "'");
			sql.append(" "+type1);
		}
		String type2 = IndexCal.getType2();
		//P00050000 病案号 P00060000 姓名  PD0100 诊断描述
		if(StringUtils.hasLength(type2)){
			sql.append(" and (instr(base.P00050000, '"+type2+"')>0 or instr(base.P00060000, '"+type2+"')>0 or instr(dep.dep_name, '"+type2+"')>0 or "
					+ " instr(dep.dep_code, '"+type2+"')>0 or instr(diag.PD0100, '"+type2+"')>0 or instr(drg.drg_code, '"+type2+"')>0 ) ");
		}
		Page<Object[]> medicalList = getQueryChannelService().createSqlQuery(sql.toString())
					.setPage(page, pageSize)
					.pagedList();
		return medicalList;
				   
	}
	
	@Override
	public Object getDRGsStdBy(String drgCode, String stdYear, String quotaCode, String pType){
		StringBuilder sql = new StringBuilder();
		sql.append(" select manage_benchmark from drgs.t_quotamanage \n ");
		sql.append(" where pardep_code is null \n ");
		sql.append(" and pardepgroup_code is null \n ");
		sql.append(" and pardrg_code = '"+drgCode+"' \n ");
		sql.append(" and manage_year='"+stdYear+"' \n ");
		sql.append(" and parquota_code='"+quotaCode+"' \n ");
		sql.append(" and patient_type='"+pType+"' \n ");
		List<Object> resultList = getRepository().createSqlQuery(sql.toString()).list();
		if(resultList.size()>0){
			return resultList.get(0);
		}
		return null;
	}
	
	@Override
	public String getLatestDate() {
		return MedicalRecord.getLatestDate();
	}

	@Override
	public List<MedicalRecord> findMedicalRecordsByDate(String date) {
//		String sql = "select * from DRGS.T_MedicalRecord md where to_char(md.outDate,'yyyy-mm') = '"+ date +"'";
//		List<MedicalRecord> medicalRecords = getQueryChannelService().createSqlQuery(sql).list();
//		return medicalRecords;
		List<MedicalRecord> medicalRecords = new LinkedList<MedicalRecord>();
		
		Pattern p = Pattern.compile(".+-.+-.+");
		Matcher m = p.matcher(date);
		Boolean dayflag = m.find();
		
		String sql = "SELECT med.id,med.patient_name,med.mr_no, "+
				"med.age_year,med.newborn_days,med.newborn_weight, "+
				"med.sex,med.in_way,med.out_way,med.in_days,"+
				"med.costamount,med.costself, "+
				"med.diagnose_dess_main,med.diagnose_code_main,med.ventilator_hour, to_char(med.in_date, 'yyyy-mm-dd') "+
				"FROM drgs.T_medicalrecord med where 1=1 ";
		if (dayflag) {
			sql += " and to_char(med.out_date,'yyyy-mm-dd')='"+date+"'";
		}else{
			sql += " and to_char(med.out_date,'yyyy-mm')='"+date+"'";
		}
		List<Object[]>  mdList = getQueryChannelService().createSqlQuery(sql.toString()).list();
		for (Object[] o : mdList) {
			MedicalRecord md = new MedicalRecord();
			md.setId(o[0]+"");
			md.setAgeYear((o[3]==null?null:Double.valueOf(o[3]+"")));
			md.setPatientName(o[1]+"");
			md.setMrNo(o[2]+"");
			md.setNewbornDays((o[4]==null?null:Double.valueOf(o[4]+"")));
			md.setNewbornWeight((o[5]==null?null:Double.valueOf(o[5]+"")));
			md.setSex(o[6]+"");
			md.setInWay(o[7]+"");
			md.setOutWay(o[8]+"");
			md.setInDays((o[9]==null?null:Double.valueOf(o[9]+"")));
			md.setCostamount((o[10]==null?null:Double.valueOf(o[10]+"")));
			md.setCostself((o[11]==null?null:Double.valueOf(o[11]+"")));
			md.setDiagnoseDessmain(o[12]+"");
			md.setDiagnoseCodemain(o[13]+"");
			md.setVentilatorHour((o[14]==null?null:Double.valueOf(o[14]+"")));
			try {
				md.setInDate(o[15]!=null?new SimpleDateFormat("yyyy-MM-dd").parse(o[15].toString()): new Date());
			} catch (ParseException e) {
				e.printStackTrace();
			}
			medicalRecords.add(md);
		}
		return medicalRecords;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.MedicalRecordApplication#getMedicalRecordNum(java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> getMedicalRecordNum(String year,String hospCode) {
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		String sql = "";
		sql =  "SELECT TO_CHAR(ipinfo.p00290000, 'MM'), COUNT(ipinfo.pmainindex) "
				+ "FROM DRGS.MR_BASEINFO BASE "
				+ "LEFT JOIN DRGS.MR_IPINFO IPINFO ON BASE.PMAININDEX=IPINFO.PMAININDEX "
				+ "WHERE TO_CHAR(ipinfo.p00290000, 'YYYY') = '"+year+"' and instr('"+hospCode+"',BASE.p00010001)>0 "
				+ "GROUP BY TO_CHAR(ipinfo.p00290000, 'MM') ";
		
		
		List<Object[]>  mdList = getQueryChannelService().createSqlQuery(sql.toString()).list();
		for (Object[] objects : mdList) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("month", objects[0]);
			map.put("num", objects[1]);
			
			resultList.add(map);
		}
		return resultList;
	}
	
	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.MedicalRecordApplication#executeSqlStr(java.lang.String)
	 */
	@Override
	public void executeSqlStr(String sql) {
		getRepository().createSqlQuery(sql).executeUpdate();
	}

	@Override
	public List<Object[]> findMedicalRecords(IndexCal IndexCal) {
		String dateFrom = IndexCal.getDateFrom();
		String dateTo = IndexCal.getDateTo();
		StringBuilder sql = new StringBuilder();
		sql.append(" select  * \n ");
		
		sql.append(" from drgs.t_medicalrecord a \n ");
		sql.append(" left join drgs.t_patientdrg b \n ");
		sql.append(" on a.id=b.parid \n ");
		sql.append(" where a.out_date between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') \n ");
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}
	
	@Override
	public List<Object[]> findDRGsFeeBy(String parid){
		StringBuilder sql = new StringBuilder();
		sql.append(" select sum(case when dcc_code='tech' then a.cost else 0 end) techFee,  \n ");
		sql.append(" sum(case when dcc_code='med' then a.cost else 0 end) medFee, \n ");
		sql.append(" sum(case when dcc_code='nurs' then a.cost else 0 end) nursFee, \n ");
		sql.append(" sum(case when dcc_code='drug' then a.cost else 0 end) drugFee, \n ");
		sql.append(" sum(case when dcc_code='manag' then a.cost else 0 end) managFee  \n ");
		sql.append(" from drgs.t_cost a, drgs.t_costsub b \n ");
		sql.append(" where a.parcost_sub_code=b.cost_sub_code \n ");
		sql.append(" and parid='"+parid+"' \n ");
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}
	
	@Override
	public List<Object[]> findDiagnosisBy(String parid){
		StringBuilder sql = new StringBuilder();
		sql.append(" select  * from drgs.t_diagnosis \n ");
		sql.append(" where parid='"+parid+"' \n ");
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}
	
	@Override
	public List<Object[]> findOperationBy(String parid){
		StringBuilder sql = new StringBuilder();
		sql.append(" select  * from drgs.t_operation \n ");
		sql.append(" where parid='"+parid+"' \n ");
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}
	
	@Override
	public List<Map<String, Object>> findMedicalRecordBy(String dateFrom, String dateTo){
		StringBuilder sql = new StringBuilder();
		sql.append(" select P00050000||'^'||to_char(P00250000,'yyyy-mm-dd') mrno,bs.pmainindex from drgs.MR_BASEINFO bs left join drgs.MR_IPINFO  ipinfo on bs.PMAININDEX=ipinfo.PMAININDEX where to_char(P00290000,'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' ");
		List<Map<String, Object>> resultList = jdbcTemplateObj.queryForList(sql.toString());
		return resultList;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.MedicalRecordApplication#findMedicalRecordByOutDate(java.lang.String, java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> findMedicalRecordByOutDate(
			String dateFrom, String dateTo) {
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		String sql = "SELECT"
					+" M.ID,"
					+" M.RECORD_ID,"
					+" M.MR_NO,"
					+" M.PATIENT_NAME,"
					+" M.SEX,"
					+" M.AGE_YEAR,"
					+" M.DAY_OF_BIRTH,"
					+" M.IN_DATE,"
					+" M.OUT_DATE,"
					+" M.IN_DEP,"
					+" M.OUT_DEP,"
					+" M.IN_WAY,"
					+" M.OUT_WAY,"
					+" M.DEP_MASTER,"
					+" M.PROTOMEDICUS,"
					+" M.PHYSICIAN,"
					+" M.RESIDENT_DOCTOR,"
					+" M.NURSE,"
					+" M.PAYMENT,"
					+" M.COSTAMOUNT,"
					+" M.COSTSELF,"
					+" M.DRUGCOST,"
					+" M.MATERIALCOST,"
					+" M.NEWBORN_WEIGHT,"
					+" M.NEWBORN_DAYS,"
					+" M.VENTILATOR_HOUR,"
					+" M.IN_DAYS,"
					+" M.DIAGNOSE_CODE_MAIN,"
					+" M.DIAGNOSE_DESS_MAIN,"
					+" M.OP_DIAGNOSIS_CODE,"
					+" M.OP_DIAGNOSIS"
					+" FROM DRGS.T_MEDICALRECORD M"
					+" WHERE M.OUT_DATE BETWEEN TO_DATE('"+dateFrom+"','YYYY-MM-DD') AND  TO_DATE('"+dateTo+"','YYYY-MM-DD')"
					+" ORDER BY M.ID";
		List<Object[]> tempList = getRepository().createSqlQuery(sql.toString()).list();
		for (Object[] o : tempList) {
			Map<String, Object> map = new HashMap<String, Object>();
			//id
			map.put("ID", o[0]);
			//就诊id
			map.put("ADM_ID", o[1]);
			//病案号
			map.put("MR_NO", o[2]);
			//姓名
			map.put("PATIENT_NAME", o[3]);
			//性别
			map.put("SEX", o[4]);
			//年龄
			map.put("AGE_YEAR", o[5]);
			//出生日期
			map.put("DAY_OF_BIRTH", o[6]);
			//入院时间
			map.put("IN_DATE", o[7]);
			//出院时间
			map.put("OUT_DATE", o[8]);
			//入院科室
			map.put("IN_DEP", o[9]);
			//出院科室
			map.put("OUT_DEP", o[10]);
			//入院途径
			map.put("IN_WAY", o[11]);
			//离院方式
			map.put("OUT_WAY", o[12]);
			//科主任
			map.put("DEP_MASTER", o[13]);
			//主任医师
			map.put("PROTOMEDICUS", o[14]);
			//主治医师
			map.put("PHYSICIAN", o[15]);
			//住院医师
			map.put("RESIDENT_DOCTOR", o[16]);
			//护士
			map.put("NURSE", o[17]);
			//医疗付费方式
			map.put("PAYMENT", o[18]);
			//总费用
			map.put("COSTAMOUNT", o[19]);
			//自费金额
			map.put("COSTSELF", o[20]);
			//药品费用
			map.put("DRUGFEE", o[21]);
			//材料费用
			map.put("MATERIALFEE", o[22]);
			//新生儿体重
			map.put("NEWBORN_WEIGHT", o[23]);
			//新生儿天数
			map.put("NEWBORN_DAYS", o[24]);
			//呼吸机使用时间
			map.put("VENTILATOR_HOUR", o[25]);
			//住院天数
			map.put("IN_DAYS", o[26]);
			//主要诊断编码
			map.put("DIAG_CODE_MAIN", o[27]);
			//主要诊断名称
			map.put("DIAG_DESC_MAIN", o[28]);
			//门急诊诊断编码
			map.put("OP_DIAGNOSIS_CODE", o[29]);
			//门急诊诊断名称
			map.put("OP_DIAGNOSIS", o[30]);
			
			resultList.add(map);
		}
		return resultList;
	}

	@Override
	public List<Object[]> findOtherDiagCodeNameById(String parid) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select d.PD0101,d.PD0100 from drgs.mr_diagnose d \n ");
		sql.append(" where d.PMAININDEX = '"+parid+"' and d.PDTYPE = 'O' \n ");
		sql.append(" order by d.PDINDEX \n ");
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}

	@Override
	public List<Object[]> findOperCodeNameById(String parid) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select op.PO0101,op.PO0100 from  \n ");
		sql.append(" drgs.mr_operation op where op.PMAININDEX = '"+parid+"' \n ");
		sql.append(" order by op.POTYPE ,op.POINDEX \n ");
		List<Object[]> resultList = getRepository().createSqlQuery(sql.toString()).list();
		return resultList;
	}
	@Override
	public List<Object[]> findDiagnosisByParIds(String parIDs) {
		String jpql="select d.PD0101,d.PD0100,d.PMAININDEX from drgs.mr_diagnose d where d.PMAININDEX in ("+parIDs+") order by d.PMAININDEX";
		List<Object[]> diagnosisList = getQueryChannelService().createJpqlQuery(jpql).list();
		return diagnosisList;
	}
	@Override
	public List<Object[]> findOperationByParIds(String parIDs) {
		String jpql="select op.PO0101,op.PO0100,op.PMAININDEX from drgs.mr_operation op where op.PMAININDEX in ("+parIDs+") order by op.PMAININDEX";
		List<Object[]> operationList = getQueryChannelService().createJpqlQuery(jpql).list();
		return operationList;
	}

	@Override
	public Integer getMedicalRecordSize(String dateFrom, String dateTo){
		String sql = "select count(*) from drgs.t_medicalrecord mr where to_date(to_char(mr.out_date,'yyyy-mm-dd'),'yyyy-mm-dd') between to_date('"+dateFrom+"', 'yyyy-mm-dd') and to_date('"+dateTo+"', 'yyyy-mm-dd') ";
		Object size = getQueryChannelService().createSqlQuery(sql).singleResult();
		Integer ssize = Integer.parseInt(size.toString());
		return ssize;
	}

	@Override
	public List<Object[]> findMedicalRecords(String dateFrom, String dateTo, int starts, int length){
		String sql = "select mr.id, mr.age_year, mr.sex, mr.costamount,mr.diagnose_code_main, mr.diagnose_dess_main, mr.in_days, mr.out_way,  "
				+ "mr.newborn_days, mr.newborn_weight, mr.ventilator_hour from drgs.t_medicalrecord mr "
				+ "where mr.out_date between to_date('"+dateFrom+" 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_date('"+dateTo+" 23:59:59', 'yyyy-mm-dd hh24:mi:ss') order by mr.id";
		Page<Object[]> medicalList = getQueryChannelService().createSqlQuery(sql.toString())
				.setPage(starts, length)
				.pagedList();
		List<Object[]> resultList = medicalList.getData();
		return resultList;
	}

	@Override
	public List<Object[]> getAllWard() {
		String sql = "select distinct(mr.out_ward_code) wardcode,mr.out_ward from drgs.t_medicalrecord mr";
		return getQueryChannelService().createSqlQuery(sql.toString()).list();
	}

	@Override
	public List<String> findMrIdByMrNoAndOutDate(String mrNo,String inDate,String OutDate) {
	   	StringBuilder sql = new StringBuilder(" select baseinfo.pmainindex from drgs.mr_baseinfo baseinfo left join drgs.mr_ipinfo ipinfo on baseinfo.pmainindex = ipinfo.pmainindex  where 1=1 ");
	   	if (StringUtils.hasLength(mrNo)) {
	   		sql.append(" and baseinfo.p00050000 = '"+mrNo+"' ");
	   	}
	   	if (StringUtils.hasLength(inDate)) {
	   		sql.append(" and to_char(ipinfo.P00250000,'yyyy-mm-dd') = '"+inDate+"' ");
	   	}
	   	if (StringUtils.hasLength(OutDate)) {
	   		sql.append(" and to_char(ipinfo.P00290000,'yyyy-mm-dd') = '"+OutDate+"' ");
	   	}
	   	
	   	List<String> mrList = getQueryChannelService()
		   .createSqlQuery(sql.toString())
		   .list();
	   	
	   	return mrList;
	}
	
	@Override
	public void creatMrBaseInfo(MrBaseInfo mr) {
		mr.save();
	}
	
	@Override
	public void createMrIpInfo(MrIpInfo mr) {
		mr.save();
	}
	
	@Override
	public void createMrAppendPage(MrAppendPage mr) {
		mr.save();
	}
	
	@Override
	public void createMrDiagnose(MrDiagnose mr){
		Map<String, Object> parameters = new HashMap<>();
		parameters.put("pMainIndex", mr.getPmainIndex());
		parameters.put("pDIndex", mr.getPdIndex());
		parameters.put("pDType", mr.getPdType());
		String sql = "SELECT 1 FROM DRGS.MR_DIAGNOSE T WHERE T.PMAININDEX=:pMainIndex AND T.PDINDEX=:pDIndex AND T.PDTYPE=:pDType";
		List<Object> list = getRepository().createSqlQuery(sql).setParameters(parameters).list();
		if(list.size()>0){
			parameters.put("pD0100", mr.getPd0100());
			parameters.put("pD0101", mr.getPd0101());
			parameters.put("pD0200", mr.getPd0200());
			parameters.put("pD0201", mr.getPd0201());
			parameters.put("pD0300", mr.getPd0300());
			parameters.put("pD0301", mr.getPd0301());
			sql = "UPDATE DRGS.MR_DIAGNOSE T set T.PD0100=:pD0100, T.PD0101=:pD0101, T.PD0200=:pD0200, T.PD0201=:pD0201, T.PD0300=:pD0300, T.PD0301=:pD0301 WHERE T.PMAININDEX=:pMainIndex AND T.PDINDEX=:pDIndex AND T.PDTYPE=:pDType";
			getRepository().createSqlQuery(sql).setParameters(parameters).executeUpdate();
		}else{
			mr.save();
		}
	}
	
	/**
	 * 
	 * @Title: getOtherDiagnoseCount
	 * @author:LiQingYang   
	 * @Description: TODO(这里用一句话描述这个方法的作用)   
	 * @param: @param pMainIndex
	 * @param: @return      
	 * @return: int      
	 * @throws
	 * @date:2017年5月10日 上午9:11:26
	 */
	public int getOtherDiagnoseCount(String pMainIndex){
        String sql = "SELECT COUNT(*)  FROM DRGS.MR_DIAGNOSE T WHERE T.PMAININDEX=:pMainIndex AND T.PDTYPE = 'O'";
        int diaOtherCount = Integer.parseInt(getQueryChannelService().createSqlQuery(sql.toString()).addParameter("pMainIndex", pMainIndex).singleResult().toString());
        return diaOtherCount;
    }
	
	/**
     * 
     * @Title: clearOtherDiagnose
     * @author:LiQingYang   
     * @Description: 清空其它诊断   
     * @param: @param pMainIndex      
     * @return: void      
     * @throws
     * @date:2017年5月10日 上午9:21:27
     */
    public  void clearOtherDiagnose(String pMainIndex){
       String  sql = "DELETE FROM DRGS.MR_DIAGNOSE T WHERE T.PMAININDEX=:PMAININDEX AND T.PDTYPE = 'O'";
       getRepository().createSqlQuery(sql).addParameter("PMAININDEX", pMainIndex).executeUpdate();
    }
	
	@Override
	public void createMrOperation(MrOperation mr){
		Map<String, Object> parameters = new HashMap<>();
		parameters.put("pMainIndex", mr.getPmainIndex());
		parameters.put("pOIndex", mr.getPoIndex());
		parameters.put("pOType", mr.getPoType());
		String sql = "SELECT 1 FROM DRGS.MR_OPERATION T WHERE T.PMAININDEX=:pMainIndex AND T.POINDEX=:pOIndex AND T.POTYPE=:pOType";
		List<Object> list = getRepository().createSqlQuery(sql).setParameters(parameters).list();
		if(list.size()>0){
			parameters.put("pO0100", mr.getPo0100());
			parameters.put("pO0101", mr.getPo0101());
			if(mr.getPo0200()!=null){parameters.put("pO0200", mr.getPo0200());}
			if(mr.getPo0300()!=null){parameters.put("pO0300", mr.getPo0300());}
			if(mr.getPo0301()!=null){parameters.put("pO0301", mr.getPo0301());}
			if(mr.getPo0400()!=null){parameters.put("pO0400", mr.getPo0400());}
			if(mr.getPo0401()!=null){parameters.put("pO0401", mr.getPo0401());}
			if(mr.getPo0500()!=null){parameters.put("pO0500", mr.getPo0500());}
			if(mr.getPo0501()!=null){parameters.put("pO0501", mr.getPo0501());}
			if(mr.getPo0600()!=null){parameters.put("pO0600", mr.getPo0600());}
			if(mr.getPo0601()!=null){parameters.put("pO0601", mr.getPo0601());}
			if(mr.getPo0700()!=null){parameters.put("pO0700", mr.getPo0700());}
			if(mr.getPo0701()!=null){parameters.put("pO0701", mr.getPo0701());}
			if(mr.getPo0800()!=null){parameters.put("pO0800", mr.getPo0800());}
			if(mr.getPo0801()!=null){parameters.put("pO0801", mr.getPo0801());}
			if(mr.getPo0900()!=null){parameters.put("pO0900", mr.getPo0900());}
			if(mr.getPo0901()!=null){parameters.put("pO0901", mr.getPo0901());}
			if(mr.getPo1000()!=null){parameters.put("pO1000", mr.getPo1000());}
			if(mr.getPo1001()!=null){parameters.put("pO1001", mr.getPo1001());}
			if(mr.getPo1100()!=null){parameters.put("pO1100", mr.getPo1100());}
			if(mr.getPo1200()!=null){parameters.put("pO1200", mr.getPo1200());}
			if(mr.getPo1201()!=null){parameters.put("pO1201", mr.getPo1201());}
			if(mr.getPo1300()!=null){parameters.put("pO1300", mr.getPo1300());}
			if(mr.getPo1301()!=null){parameters.put("pO1301", mr.getPo1301());}
			if(mr.getPo1400()!=null){parameters.put("pO1400", mr.getPo1400());}
			if(mr.getPo1500()!=null){parameters.put("pO1500", mr.getPo1500());}
			if(mr.getPo1501()!=null){parameters.put("pO1501", mr.getPo1501());}
			if(mr.getPo0201()!=null){parameters.put("pO0201", mr.getPo0201());}
			sql = "UPDATE DRGS.MR_OPERATION T SET "
					+ "T.PO0100=:pO0100, "
					+ "T.PO0101=:pO0101, "
					+ (mr.getPo0200()!=null?("T.PO0200=:pO0200, "):("T.PO0200 = null, "))
					+ (mr.getPo0300()!=null?("T.PO0300=:pO0300, "):("T.PO0300 = null, "))
					+ (mr.getPo0301()!=null?("T.PO0301=:pO0301, "):("T.PO0301 = null, "))
					+ (mr.getPo0400()!=null?("T.PO0400=:pO0400, "):("T.PO0400 = null, "))
					+ (mr.getPo0401()!=null?("T.PO0401=:pO0401, "):("T.PO0401 = null, "))
					+ (mr.getPo0500()!=null?("T.PO0500=:pO0500, "):("T.PO0500 = null, "))
					+ (mr.getPo0501()!=null?("T.PO0501=:pO0501, "):("T.PO0501 = null, "))
					+ (mr.getPo0600()!=null?("T.PO0600=:pO0600, "):("T.PO0600 = null, "))
					+ (mr.getPo0601()!=null?("T.PO0601=:pO0601, "):("T.PO0601 = null, "))
					+ (mr.getPo0700()!=null?("T.PO0700=:pO0700, "):("T.PO0700 = null, "))
					+ (mr.getPo0701()!=null?("T.PO0701=:pO0701, "):("T.PO0701 = null, "))
					+ (mr.getPo0800()!=null?("T.PO0800=:pO0800, "):("T.PO0800 = null, "))
					+ (mr.getPo0801()!=null?("T.PO0801=:pO0801, "):("T.PO0801 = null, "))
					+ (mr.getPo0900()!=null?("T.PO0900=:pO0900, "):("T.PO0900 = null, "))
					+ (mr.getPo0901()!=null?("T.PO0901=:pO0901, "):("T.PO0901 = null, "))
					+ (mr.getPo1000()!=null?("T.PO1000=:pO1000, "):("T.PO1000 = null, "))
					+ (mr.getPo1001()!=null?("T.PO1001=:pO1001, "):("T.PO1001 = null, "))
					+ (mr.getPo1100()!=null?("T.PO1100=:pO1100, "):("T.PO1100 = null, "))
					+ (mr.getPo1200()!=null?("T.PO1200=:pO1200, "):("T.PO1200 = null, "))
					+ (mr.getPo1201()!=null?("T.PO1201=:pO1201, "):("T.PO1201 = null, "))
					+ (mr.getPo1300()!=null?("T.PO1300=:pO1300, "):("T.PO1300 = null, "))
					+ (mr.getPo1301()!=null?("T.PO1301=:pO1301, "):("T.PO1301 = null, "))
					+ (mr.getPo1400()!=null?("T.PO1400=:pO1400, "):("T.PO1400 = null, "))
					+ (mr.getPo1500()!=null?("T.PO1500=:pO1500, "):("T.PO1500 = null, "))
					+ (mr.getPo1501()!=null?("T.PO1501=:pO1501, "):("T.PO1501 = null, "))
					+ (mr.getPo0201()!=null?("T.PO0201=:pO0201 "):("T.PO0201 = null  "))
					+ "WHERE T.PMAININDEX=:pMainIndex AND T.POINDEX=:pOIndex AND T.POTYPE=:pOType";
			getRepository().createSqlQuery(sql).setParameters(parameters).executeUpdate();
		}else{
			mr.save();
		}
	}
	
	/**
	 * 
	 * <p>Title: getOtherOperationCount</p>   
	 * <p>Description: </p>   
	 * @param pMainIndex
	 * @return   
	 * @see com.dhcc.DRGsHosp.application.MedicalRecordApplication#getOtherOperationCount(java.lang.String)
	 */
	public int getOtherOperationCount(String pMainIndex){
        String sql = "SELECT COUNT(*)  FROM DRGS.MR_OPERATION T WHERE T.PMAININDEX=:pMainIndex AND T.POTYPE = 'O'";
        int operOtherCount = Integer.parseInt(getQueryChannelService().createSqlQuery(sql.toString()).addParameter("pMainIndex", pMainIndex).singleResult().toString());
        return operOtherCount;
    }
    
   
	/**
	 * 
	 * <p>Title: clearOtherOperation</p>   
	 * <p>Description: </p>   
	 * @param pMainIndex   
	 * @see com.dhcc.DRGsHosp.application.MedicalRecordApplication#clearOtherOperation(java.lang.String)
	 */
    public  void clearOtherOperation(String pMainIndex){
       String  sql = "DELETE FROM DRGS.MR_OPERATION T WHERE T.PMAININDEX=:PMAININDEX AND T.POTYPE = 'O'";
       getRepository().createSqlQuery(sql).addParameter("PMAININDEX", pMainIndex).executeUpdate();
    }
    
	/**
	 * 
	 * <p>Title: createMrExpense</p>   
	 * <p>Description: </p>   
	 * @param mr   
	 * @see com.dhcc.DRGsHosp.application.MedicalRecordApplication#createMrExpense(com.dhcc.DRGsHosp.core.domain.MrExpense)
	 */
	@Override
	public void createMrExpense(MrExpense mr){
		Map<String, Object> parameters = new HashMap<>();
		parameters.put("pMainIndex", mr.getPmainIndex());
		parameters.put("peIndex", mr.getPeIndex());
		String sql = "SELECT 1 FROM DRGS.MR_EXPENSE T WHERE T.PMAININDEX=:pMainIndex AND T.PEINDEX=:peIndex";
		List<Object> list = getRepository().createSqlQuery(sql).setParameters(parameters).list();
		if(list.size()>0){
			parameters.put("pE0200", mr.getPe0200());
			parameters.put("pE0101", mr.getPe0101());
			sql = "UPDATE DRGS.MR_EXPENSE T set T.PE0200=:pE0200,T.PE0101=:pE0101 WHERE T.PMAININDEX=:pMainIndex AND T.PEINDEX=:peIndex";
			getRepository().createSqlQuery(sql).setParameters(parameters).executeUpdate();
		}else{
			mr.save();
		}
	}
	
	@Override
	public void createMrExpenseSum(MrExpenseSum mr){
		mr.save();
	}
	
	/**
	 * 
	 * @Title: getMrExpenseCount
	 * @author:LiQingYang   
	 * @Description: 获取病例费用总行数  
	 * @param: @param pMainIndex
	 * @param: @return      
	 * @return: int      
	 * @throws
	 * @date:2017年5月23日 下午4:35:47
	 */
	public int getMrExpenseCount(String pMainIndex){
        String sql = "SELECT COUNT(*)  FROM DRGS.MR_EXPENSE T WHERE T.PMAININDEX=:pMainIndex";
        int mrExpenseCount = Integer.parseInt(getQueryChannelService().createSqlQuery(sql.toString()).addParameter("pMainIndex", pMainIndex).singleResult().toString());
        return mrExpenseCount;
    }
	
	/**
	 * 
	 * @Title: clearMrExpense
	 * @author:LiQingYang   
	 * @Description: 删除病例费用   
	 * @param: @param pMainIndex      
	 * @return: void      
	 * @throws
	 * @date:2017年5月23日 下午4:34:23
	 */
	 public  void clearMrExpense(String pMainIndex){
       String  sql = "DELETE FROM DRGS.MR_EXPENSE T WHERE T.PMAININDEX=:PMAININDEX";
       getRepository().createSqlQuery(sql).addParameter("PMAININDEX", pMainIndex).executeUpdate();
    }
	 
	 
	/**
	 * 
	 * <p>Title: saveTransData</p>   
	 * <p>Description: </p>   
	 * @param transData   
	 * @see com.dhcc.DRGsHosp.application.MedicalRecordApplication#saveTransData(java.util.Map)
	 */
	@Override
	public void saveTransData(Map<String, Object> transData) {
		String pMainIndex = transData.get("pmainindex")!=null?transData.get("pmainindex").toString():"";
		List<Map<String, Object>> transList = (List<Map<String, Object>>) transData.get("transData");	
		String st = "select count(1) from drgs.mr_transdep where pmainindex='"+pMainIndex+"'";
		Object transCntObj = getRepository().createSqlQuery(st).singleResult();
		int transCnt_o = Integer.parseInt(transCntObj.toString());
		int transCnt_n = transList.size();
		StringBuilder sql = new StringBuilder("declare \n ");
		sql.append("  ecnt integer; \n ");
		sql.append("begin \n ");
		for(Map<String, Object> trans: transList){
			sql.append("  select count(1) into ecnt from drgs.mr_transdep where pmainindex='"+pMainIndex+"' and ptindex="+trans.get("ptindex")+"; \n ");
			sql.append("  if ecnt>0 then \n ");
			sql.append("    update drgs.mr_transdep set pt0020='"+trans.get("pt0200")+"', pt0021='"+trans.get("pt0021")+"', pt0010='"+trans.get("pt0010")+"', pt0011='"+trans.get("pt0011")+"', pt0030="+trans.get("pt0030")+" where pmainindex='"+pMainIndex+"' and ptindex="+trans.get("ptindex")+"; \n ");
			sql.append("  else \n ");
			sql.append("    insert into drgs.mr_transdep(pmainindex, ptindex, pt0011, pt0010, pt0021, pt0020, pt0030) values('"+pMainIndex+"', "+trans.get("ptindex")+", '"+trans.get("pt0011")+"', '"+trans.get("pt0010")+"', '"+trans.get("pt0021")+"', '"+trans.get("pt0020")+"', "+trans.get("pt0030")+"); \n ");
			sql.append("  end if; \n ");
		}
		if(transCnt_o > transCnt_n){
			//清除多余数据
			sql.append("  select count(1) into ecnt from drgs.mr_transdep where pmainindex='"+pMainIndex+"'; \n ");
			sql.append("  if ecnt>"+transCnt_n+" then \n ");
			sql.append("    delete from drgs.mr_transdep where pmainindex='"+pMainIndex+"' and ptindex>="+transCnt_n+"; \n ");
			sql.append("  end if; \n ");
		}
		sql.append("commit; \n ");
		sql.append("end; \n ");
		getRepository().createSqlQuery(sql.toString()).executeUpdate();
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.MedicalRecordApplication#findPatientsByQuota(com.dhcc.DRGsHosp.core.domain.IndexCal)
	 */
	@Override
	public List<Map<String, Object>> findPatientsByQuota(IndexCal IndexCal) {
		Map<String, Object> quotaCompute = new HashMap<String, Object>();
		quotaCompute.put("Risk1CaseCnt", "and c.as_risk_level=1");
		quotaCompute.put("Risk1DeadCaseCnt", "and a.out_way='5' and c.as_risk_level=1");
		quotaCompute.put("Risk2CaseCnt", "and c.as_risk_level=2");
		quotaCompute.put("Risk2DeadCaseCnt", "and a.out_way='5' and c.as_risk_level=2");
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select ID,RECORD_ID,MR_NO,PATIENT_NAME,SEX,AGE_YEAR,DIAGNOSE_DESS_MAIN,OUT_DEP \n ");
		sql.append(" from DRGs.t_medicalrecord a \n ");
		sql.append(" left join DRGs.t_patientdrg b on a.id=b.parid \n ");
		sql.append(" left join drgs.t_drgs drg  on drg.DRG_CODE=b.PARDRG_CODE and b.VER_CODE=drg.VER_CODE  \n ");
		sql.append(" left join DRGs.t_department dep on a.out_dep_code=dep.dep_code  \n ");
		sql.append(" left join DRGs.t_annualstd c  on b.pardrg_code=c.as_drg_code and as_year="+IndexCal.getStdYear()+" \n ");
		sql.append(" where 1=1 \n ");
		sql.append(" and a.out_date between to_date('"+IndexCal.getDateFrom()+"', 'yyyy-mm-dd hh24:mi:ss') and to_date('"+IndexCal.getDateTo()+"','yyyy-mm-dd hh24:mi:ss') ");
		if (quotaCompute.get(IndexCal.getQuotaCode()) != null) {
			sql.append(quotaCompute.get(IndexCal.getQuotaCode()));
		}
		if (IndexCal.getDeps() != null) {
			sql.append(" and a.out_dep_code = '"+IndexCal.getDeps()+"'");
		}
		List<Object[]> dataList = getRepository().createSqlQuery(sql.toString()).list();
		List<Map<String, Object>> medicalRecords = new ArrayList<Map<String, Object>>();
		for (Object[] o : dataList) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("ID",o[0]);
			map.put("RECORD_ID",o[1]);
			map.put("MR_NO",o[2]);
			map.put("PATIENT_NAME",o[3]);
			map.put("SEX",o[4]);
			map.put("AGE",o[5]);
			map.put("DIAGNOSE_MAIN",o[6]);
			map.put("OUT_DEP",o[7]);
			
			medicalRecords.add(map);
		}
		return medicalRecords;
	}

	@Override
	public boolean isMedicalRecordsExistByDate(String date) {
		// TODO Auto-generated method stub
		Pattern p = Pattern.compile(".+-.+-.+");
		Matcher m = p.matcher(date);
		Boolean dayflag = m.find();
		String sql = "select count(1) from drgs.mr_ipinfo ipinfo where 1=1 ";
		if (dayflag) {
			sql += " and to_char(ipinfo.p00290000,'yyyy-mm-dd')='"+date+"'";
		}else{
			sql += " and to_char(ipinfo.p00290000,'yyyy-mm')='"+date+"'";
		}
		List<Object> result  = getQueryChannelService().createSqlQuery(sql).list() ;
		BigInteger count =new BigInteger(result.get(0).toString()) ; 
		if( count.longValue() > 0) {
			return true ;
		}
		return false ;
	}
	
	@Override
	public List<Object[]> getPatientListByDay(String dateFrom, String dateTo){
		StringBuilder sql = new StringBuilder();
		sql.append(" select a.pmainindex \n ");
		sql.append(" , a.p00010000, a.p00010001, a.p00020000, a.p00020001, a.p00030000 --5 \n ");
		sql.append(" , a.p00040000, a.p00050000, a.p00060000, a.p00070000, a.p00070001 --10 \n ");
		sql.append(" , to_char(a.p00080000, 'yyyy-mm-dd') as p00080000                 --11\n ");
		sql.append(" , a.p00090000, a.P00090002 as P00090100                           --13 \n ");
		sql.append(" , a.p00100000, a.p00110000, a.p00120000                           --16 \n ");
		sql.append(" , a.p00130000                                                     --17 \n ");
		sql.append(" , a.p00130100, a.p00130200, a.p00130300, a.p00140000, a.p00140100 --22 \n ");
		sql.append(" , a.p00140200, a.p00150000, a.p00160000, a.p00170000              --26 \n ");
		sql.append(" , a.p00180000, a.p00190000, a.p00190100, a.p00190200, a.p00190300 --31 \n ");
		sql.append(" , a.p00190400, a.p00190500, a.p00190600, a.p00200000, a.p00200100 --36 \n ");
		sql.append(" , a.p00200200, a.p00200300, a.p00200400, a.p00200500, a.p00210000 --41 \n ");
		sql.append(" , a.p00210100, a.p00210200, a.p00220000, a.p00220100, a.p00220200 --46 \n ");
		sql.append(" , a.p00220300, a.p00220400                                        --48\n ");
		sql.append(" , P00230000, P00230001                                            --50\n ");
		sql.append(" , to_char(P00250000, 'yyyy-mm-dd') as P00250000                   --51 \n ");
		sql.append(" , to_char(P00250000, 'hh24:mi:ss') as P00250100                    \n ");
		sql.append(" , P00260000, P00260001                                            --54 \n ");
		sql.append(" , P00270000, P00270001, P00280000, P00280001                      --58 \n ");
		sql.append(" , to_char(P00290000, 'yyyy-mm-dd') as P00290000                   --59 \n ");
		sql.append(" , to_char(P00290000, 'hh24:mi:ss') as P00290100                   --60 \n ");
		sql.append(" , P00300000, P00300001, P00310000, P00310001, P00320000           --65 \n ");
		sql.append(" , P00380000 , P00380001                                           --67 \n ");
		sql.append(" , to_char(P00390000, 'yyyy-mm-dd') as P00390000                    \n ");
		sql.append(" , P00400100, P00400200, P00400300, P00410000                      --72 \n ");
		sql.append(" , P00410001, P00420000, P00420001, P00430000, P00430001           --77 \n ");
		sql.append(" , P00440000, P00440001, P00450000, P00450001, P00460000           --82 \n ");
		sql.append(" , P00460001, P00470000, P00470001, P00480000, P00480001           --87 \n ");
		sql.append(" , P00490000, P00500000, P00510000, P00520000, P00530000           --92 \n ");
		sql.append(" , P00540000, P00550000, P00550101, P00550102, P00550103           --97 \n ");
		sql.append(" , P00560000, P00570000, P00570001, P00580000, P00580001           --102 \n ");
		sql.append(" , P00590000, P00590100, P00590200, P00590300, P00590400           --107 \n ");
		sql.append(" , P00590500, P00600100, P00600101, P00600200, P00600201           --112 \n ");
		sql.append(" , P00600300, P00600301, P00600400, P00600401, P00600500           --117 \n ");
		sql.append(" , P00600600, P00600700, P00600800, P00600900, P00610000           --122 \n ");
		sql.append(" , P00610001, P00610100, P00610200 \n ");
		sql.append(" , to_char(P00610300, 'yyyy-mm-dd') as P00610300                   --126 \n ");
		sql.append(" , P00620000, P00620001, P00620100, P00620200, P00630000           --131 \n ");
		sql.append(" , P00630001, P00630100, P00640001, P00640002, P00640003           --136 \n ");
		sql.append(" , P00650001, P00650002, P00650003                                 --139 \n ");
		sql.append(" , P90120201 as P00670001                                          --140 \n ");
		sql.append(" , P00950000 as P00680101 \n ");
		sql.append(" , P00950001 as P00680102 \n ");
		sql.append(" , P00950002 as P00680103 \n ");
		sql.append(" , P00960000 as P80020100 \n ");
		sql.append(" , P00960001 as P80020200     \n ");
		sql.append(" , P00800000 as P60000000          \n ");
		sql.append(" , P00810000 as P60000100                                          --147 \n ");
		sql.append(" , P00930000, to_char(P00930001, 'yyyy-mm-dd') P00930001           --149 \n ");
		sql.append(" , to_char(P00930002, 'yyyy-mm-dd') P00930002 \n ");
		sql.append(" , P00940001, P00940002, P00400101, P00400401, P00900000, P00900001 \n ");
		sql.append("  \n ");
		sql.append(" from drgs.mr_baseinfo a  \n ");
		sql.append(" left join drgs.mr_ipinfo b on a.pmainindex=b.pmainindex \n ");
		sql.append(" left join drgs.mr_appendpage c on a.pmainindex=c.pmainindex \n ");
		sql.append(" where to_char(a.PUSH_DATE, 'yyyy-mm-dd') between '"+dateFrom+"' and '"+dateTo+"' \n ");
//		sql.append(" and rownum<6 \n ");
		List<Object[]> rsList = getRepository().createSqlQuery(sql.toString()).list();
		return rsList;
	}
	
	@Override
	public List<Object[]> getDiagnoseListById(String pmainindex){
		String sql = "select pmainindex, pdtype, pd0100, pd0101, pd0200, pd0201, pd0300, pd0301 from drgs.mr_diagnose where pmainindex='"+pmainindex+"' order by pdtype, pdindex";
		List<Object[]> rsList = getRepository().createSqlQuery(sql).list();
		return rsList;
	}
	
	@Override
	public List<Object[]> getOperationListById(String pmainindex){
		StringBuilder sql = new StringBuilder();
		sql.append(" select pmainindex, po0100, po0101, \n ");
		sql.append(" to_char(po0200, 'yyyy-mm-dd') as po0200, \n ");
		sql.append(" po0300, po0301, po0400, po0401, po0500, po0501, po0600, po0601, \n ");
		sql.append(" po0700, po0701, po0800, po0801, po0900, po0901 \n ");
		sql.append(" from drgs.mr_operation \n ");
		sql.append(" where pmainindex='"+pmainindex+"' \n ");
		sql.append(" order by poindex \n ");
		List<Object[]> rsList = getRepository().createSqlQuery(sql.toString()).list();
		return rsList;
	}
	
	@Override
	public List<Object[]> getExpenseListById(String pmainindex){
		StringBuilder sql = new StringBuilder();
		sql.append(" select * from ( \n ");
		sql.append(" select pmainindex, pe0101, pe0200  \n ");
		sql.append(" from drgs.mr_expense  \n ");
		sql.append(" where pmainindex='"+pmainindex+"' \n ");
		sql.append(" ) pivot ( \n ");
		sql.append(" sum(pe0200) for pe0101 in  \n ");
		sql.append(" (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28) \n ");
		sql.append(" ) \n ");
		List<Object[]> rsList = getRepository().createSqlQuery(sql.toString()).list();
		return rsList;
	}
}
